﻿<%@ WebHandler Language="C#" Class="ZSProblemReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 问题上报查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.4.26
 **/

public class ZSProblemReport : IHttpHandler
{
    public bool IsReusable { get { return false; } }
    private uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getSearchSqlString(JObject jObject)
    {
        string sql = @"
SELECT up.description,
       up.ufccontent,
       e.fullname createPerson,
       w.workcentername,
       e2.fullname responsiblePerson,
       up.ufccreatedate,
       up.ufcchulidate,
       CASE WHEN up.ufcstatus=0 THEN '已关闭' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' 
            WHEN up.ufcstatus=3 THEN '处理完' 
       END status,
       DECODE(up.ufcwtdj,'3','一般（3天）',2,'急（1天）',1,'紧急（4H）',0,'特急（1H）') priority,
       CASE WHEN (up.ufcstatus = 0 OR up.ufcstatus = 3) AND NVL(up.ufcguanbidate,up.ufcguanbidate) IS NOT NULL 
                  THEN ROUND((NVL(up.ufcguanbidate,up.ufcguanbidate)-up.ufcchulidate),2)
            WHEN (up.ufcstatus = 0 OR up.ufcstatus = 3) AND NVL(up.ufcguanbidate,up.ufcguanbidate) IS NULL THEN 0
            WHEN (up.ufcstatus = 1 OR up.ufcstatus = 2) THEN ROUND((SYSDATE-up.ufcchulidate),2) 
       END delayday,
       up.ufcresult,
       w2.workcentername createWorkcenter,
       up.notes,
       up.ufcguanbidate,
       up.ufcwanchengdate
FROM ufcproblem up
LEFT JOIN employee e ON e.employeeid = up.ufccreateemployeeid
LEFT JOIN employee e2 ON e2.employeeid = up.ufctoemployeeid
LEFT JOIN workcenter w ON w.workcenterid = up.workcenterid
LEFT JOIN sessionvalues sv ON sv.employeeid = up.ufccreateemployeeid
LEFT JOIN workcenter w2 ON w2.workcenterid = sv.workcenterid
LEFT JOIN container c ON c.containerid = up.containerid
WHERE 1=1 
";
        //主制分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND up.factoryid='{0}'", jObject["factoryid"].ToString());
        }

        //提出单位
        if (jObject["createworkcenterid"] != null && jObject["createworkcenterid"].ToString() != "")
        {
            sql += string.Format(" AND sv.workcenterid = '{0}'", jObject["createworkcenterid"].ToString());
        }

        //责任单位
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND w.workcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        //批次号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND c.containername LIKE '%{0}%'", jObject["containername"].ToString());
        }

        //提出人
        if (jObject["creater"] != null && jObject["creater"].ToString() != "")
        {
            sql += string.Format(" AND up.ufccreateemployeeid = '{0}'", jObject["creater"].ToString());
        }

        //责任人
        if (jObject["responser"] != null && jObject["responser"].ToString() != "")
        {
            sql += string.Format(" AND up.ufctoemployeeid = '{0}'", jObject["responser"].ToString());
        }


        //提出时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND up.ufccreatedate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND up.ufccreatedate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        //状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "未处理")
            {
                sql += " AND up.ufcstatus = 1 ";
            }
            if (jObject["status"].ToString() == "处理中")
            {
                sql += " AND up.ufcstatus = 2 ";
            }
            if (jObject["status"].ToString() == "处理完")
            {
                sql += " AND up.ufcstatus = 3 ";
            }
            if (jObject["status"].ToString() == "已关闭")
            {
                sql += " AND up.ufcstatus = 0 ";
            }
        }

        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        foreach (DataRow dr in dt.Rows)
        {
            if (dr["delayday"].ToString() != "" && Convert.ToDouble(dr["delayday"].ToString()) < 0)
            {
                dr["delayday"] = 0;
            }
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        foreach (DataRow dr in dt.Rows)
        {
            if (dr["delayday"].ToString() != "" && Convert.ToDouble(dr["delayday"].ToString()) < 0)
            {
                dr["delayday"] = 0;
            }
        }
        dt.Columns["description"].Caption = "问题简述";
        dt.Columns["notes"].Caption = "问题详述";
        dt.Columns["ufcresult"].Caption = "处理意见";
        dt.Columns["ufccontent"].Caption = "问题类型";
        dt.Columns["responsibleperson"].Caption = "责任人";
        dt.Columns["workcentername"].Caption = "责任单位";
        dt.Columns["createperson"].Caption = "提出人";
        dt.Columns["createworkcenter"].Caption = "提出单位";
        dt.Columns["ufccreatedate"].Caption = "问题提出时间";
        dt.Columns["ufcchulidate"].Caption = "要求完成时间";
        dt.Columns["ufcwanchengdate"].Caption = "问题完成时间";
        dt.Columns["ufcguanbidate"].Caption = "问题关闭时间";
        dt.Columns["status"].Caption = "状态";
        dt.Columns["priority"].Caption = "需求程度";
        dt.Columns["delayday"].Caption = "超期天数";
        ExportHelper.ExportExcel(dt, jObject["factoryname"].ToString() + "问题上报查询报表");
    }
}